package com.demo.model.base;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.demo.dto.OrderCondition;
import com.demo.dto.QueryCondition;
import com.demo.kit.StrKit;
import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.plugin.activerecord.TableMapping;
import com.jfinal.plugin.redis.Cache;
import com.jfinal.plugin.redis.Redis;

public abstract class SqlModel<M extends SqlModel<M>> extends Model<M>{

	protected static int REDIS_CACHE_EXPIRE = 5 * 60;
	private static final long serialVersionUID = 1L;
	// sql查询的字段，默认字段则为 select *,子类可重写 如 select id ,username 
	protected String preFixSql = "select * ";
	protected Boolean HAS_DEL_KEY = false;
	protected final String DEL_KEY = "status";
	protected final int DEL_VALUE = 1;
	protected String[] search_col = {};
	
	protected String[] getSearchCol(){
		return search_col;
	}
	public void setSearch_col(String[] search_col) {
		this.search_col = search_col;
	}
	protected String getPreFixSql() {
		return preFixSql;
	}
	protected void setPreFixSql(String preFixSql) {
		this.preFixSql = preFixSql;
	}
	protected Boolean checkDelKey() {
		return HAS_DEL_KEY;
	}
	protected Boolean setDelKey(boolean tag) {
		return HAS_DEL_KEY = tag;
	}
	protected String getDelName() {
		return DEL_KEY;
	}
	protected int getDelValue() {
		return DEL_VALUE;
	}
	
	public Integer getInt(String attr) {
		if(super.getAttrs().containsKey(attr)){
			return super.getBigDecimal(attr).intValue();
		}
		return null;
	}
	
	public Integer getInt(String attr , Integer defaultValue) {
		if(super.getAttrs().containsKey(attr) && super.getBigDecimal(attr)!=null){
			return super.getBigDecimal(attr).intValue();
		}
		return defaultValue;
	}
	
	public Long getLong(String attr) {
		return super.getNumber(attr).longValue();
	}

	public Double getDouble(String attr) {
		return super.getNumber(attr).doubleValue();
	}

	public boolean containsKey(String key) {
		return super.getAttrs().containsKey(key);
	}

	public String getTableName(){
		return TableMapping.me().getTable(getClass()).getName();
	}
	
	@SuppressWarnings("rawtypes")
	public List<Record> listAll(Map cond){
		SqlPara sqlPara = Db.getSqlPara("common.findByParams", Kv.by("cond", cond).set("tableName", this.getTableName()));
		return Db.find(sqlPara);
	}
	
	public Record getByParams(Map cond){
		SqlPara sqlPara = Db.getSqlPara("common.findByParams", Kv.by("cond", cond).set("tableName", this.getTableName()));
		return Db.findFirst(sqlPara);
	}
	
	public Record getById(int id){
		Kv cond = Kv.by("id = ", id);
		return getByParams(cond);
	}
	
	
	
	
	
	/**
	 * 根据缓存查询(redis缓存)
	 * @param cacheName				redis缓存名称
	 * @param key					cache.set(key, value);
	 * @param conditionList			查询参数
	 * @param orderConditionList	排序参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<M> listByCache(String cacheName, String key, List<QueryCondition> conditionList, List<OrderCondition> orderConditionList){
		Cache cache = Redis.use(cacheName);
		if (cache.exists(key)) {
			return cache.get(key);
		}
		StringBuilder sql = new StringBuilder(getPreFixSql()+" from "+this.getTableName()+" where ");
		Map<String, Object> sqlMap = this.getWhereStr(conditionList);
		sql.append(sqlMap.get("sql")).append(getOrderStr(orderConditionList));
		List<Object> valuesList = (List<Object>) sqlMap.get("values");
		Object[] paras = valuesList.toArray();
//		cache.set(key, find(sql.toString(), paras));
		// 设置缓存时间单位秒
		cache.setex(key, REDIS_CACHE_EXPIRE, find(sql.toString(), paras));
		return find(sql.toString(), paras);
	}
	public List<M> listByCache(String cacheName, String key){
		return listByCache(cacheName, key, new ArrayList<QueryCondition>(), new ArrayList<OrderCondition>());
	}
	
	public List<M> listByCache(String cacheName, String key, List<QueryCondition> conditionList){
		return listByCache(cacheName, key, conditionList, new ArrayList<OrderCondition>());
	}
	
	public List<M> listByCache(String cacheName, String key, QueryCondition condition){
		List<QueryCondition> conditionList = new ArrayList<QueryCondition>();
		conditionList.add(condition);
		return listByCache(cacheName, key, conditionList, new ArrayList<OrderCondition>());
	}
	public List<M> listByCache(String cacheName, String key, QueryCondition condition, List<OrderCondition> orderConditionList){
		List<QueryCondition> conditionList = new ArrayList<QueryCondition>();
		conditionList.add(condition);
		return listByCache(cacheName, key, conditionList, orderConditionList);
	}
	public List<M> listByCache(String cacheName, String key, QueryCondition condition, OrderCondition orderCondition){
		List<QueryCondition> conditionList = new ArrayList<QueryCondition>();
		conditionList.add(condition);
		List<OrderCondition> orderConditionList = new ArrayList<OrderCondition>();
		orderConditionList.add(orderCondition);
		return listByCache(cacheName, key, conditionList, orderConditionList);
	}
	
	/**
	 * 多条件,默认排序,查询所有数据
	 * @param conditionList	参数list
	 * @return
	 */
	public List<M> listByParams(List<QueryCondition> conditionList)
	{
		return listByParams(conditionList, new ArrayList<OrderCondition>());
	}
	
	/**
	 * 多条件,单排序,查询所有数据 
	 * @param conditionList
	 * @param orderCondition
	 * @return
	 */
	public List<M> listByParams(List<QueryCondition> conditionList, OrderCondition orderCondition)
	{
		List<OrderCondition> orderConditionList = new ArrayList<OrderCondition>();
		orderConditionList.add(orderCondition);
		return listByParams(conditionList, orderConditionList);
	}
	
	/**
	 * 单条件,默认排序,查询所有数据
	 * @param condition
	 * @return
	 */
	public List<M> listByParams(QueryCondition condition)
	{
		List<QueryCondition> conditionList = new ArrayList<QueryCondition>();
		conditionList.add(condition);
		return listByParams(conditionList, new ArrayList<OrderCondition>());
	}
	
	/**
	 * 单条件,多排序,查询所有数据
	 * @param condition
	 * @param orderConditionList
	 * @return
	 */
	public List<M> listByParams(QueryCondition condition, List<OrderCondition> orderConditionList)
	{
		List<QueryCondition> conditionList = new ArrayList<QueryCondition>();
		conditionList.add(condition);
		return listByParams(conditionList, orderConditionList);
	}
	
	/**
	 * 单条件,单排序,查询所有数据
	 * @param condition
	 * @param orderCondition
	 * @return
	 */
	public List<M> listByParams(QueryCondition condition, OrderCondition orderCondition)
	{
		List<QueryCondition> conditionList = new ArrayList<QueryCondition>();
		conditionList.add(condition);
		List<OrderCondition> orderConditionList = new ArrayList<OrderCondition>();
		orderConditionList.add(orderCondition);
		return listByParams(conditionList, orderConditionList);
	}
	
	/**
	 * 多条件,多排序,查询所有数据
	 * @param conditionList
	 * @param orderConditionList
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<M> listByParams(List<QueryCondition> conditionList, List<OrderCondition> orderConditionList)
	{
		StringBuilder sql = new StringBuilder(getPreFixSql()+" from "+this.getTableName()+" where ");
		Map<String, Object> sqlMap = this.getWhereStr(conditionList);
		sql.append(sqlMap.get("sql")).append(getOrderStr(orderConditionList));
		List<Object> valuesList = (List<Object>) sqlMap.get("values");
		Object[] params = valuesList.toArray();
		return this.find(sql.toString(), params);
	}
	
	/**
	 * 多条件查询
	 * @param conditionList
	 * @return
	 */
	public M getByParams(List<QueryCondition> conditionList)
	{
		StringBuilder sql = new StringBuilder();
		sql.append(getPreFixSql());
		sql.append(" from ");
		sql.append(this.getTableName());
		sql.append(" where ");
		Map<String, Object> sqlMap = this.getWhereStr(conditionList);
		sql.append(sqlMap.get("sql"));
		@SuppressWarnings("unchecked")
		List<Object> valuesList = (List<Object>) sqlMap.get("values");
		Object[] params = valuesList.toArray();
		return this.findFirst(sql.toString(), params);
	}
	
	/**
	 * 单条件查询
	 * @param queryCondition
	 * @return
	 */
	public M getByParams(QueryCondition queryCondition)
	{
		List<QueryCondition> conditionList = new ArrayList<QueryCondition>();
		conditionList.add(queryCondition);
		return getByParams(conditionList);
	}
	
	/**
	 * 组装sql(多条件查询)
	 * @param tableName	表名
	 * @param conditionList	包含查询条件的对象
	 * @param orderKey	排序的字段
	 * @param order		排序的方向
	 * @return	返回的map，带有参数的sql和对应值的sql
	 */
	public Map<String, Object> getSql(List<QueryCondition> conditionList) {
		Map<String, Object> sqlMap = new HashMap<String, Object>();
		StringBuilder sb = new StringBuilder();
		sb.append("from ").append(getTableName()).append(" where 1=1");
		List<Object> values = new ArrayList<Object>();
		if(conditionList.size() != 0){
			Iterator<QueryCondition> iterator = conditionList.iterator();
			while(iterator.hasNext()){
				QueryCondition queryCondition = iterator.next();
				String key = queryCondition.getKey();
				String op = queryCondition.getOp();
				switch (op) {
				case QueryCondition.NOT_NULL:
					sb.append(" and ").append(key).append(op);
					break;
				default:
					Object value = queryCondition.getValue();
					sb.append(" and ").append(key).append(op).append("?");
					values.add(value);
					break;
				}
			}
		}
		if (checkDelKey()) {
			sb.append(" and " + getDelName() + " <> " + getDelValue());
		}
		sqlMap.put("param", sb.toString());
		sqlMap.put("value", values);
		return sqlMap;
	}
	
	/**
	 * 拼装不带where的where子句，至少返回 1 = 1 ，外面可以放心拼接；
	 * in 和 not in 没有经过验证，感觉有坑，可能会出问题，
	 * 目前没有使用场景，使用的时候再做验证；
	 * right like 效率过低，不建议使用。
	 * @param conditionList where条件的List
	 * @return Map<String, Object> 共计两个key，分别是sql和values
	 */
	protected Map<String, Object> getWhereStr(List<QueryCondition> conditionList) {
		StringBuilder sb = new StringBuilder(" 1 = 1 ");
		List<Object> values = new ArrayList<Object>();
		if(conditionList.size() != 0){
			Iterator<QueryCondition> iterator = conditionList.iterator();
			while(iterator.hasNext()){
				QueryCondition queryCondition = iterator.next();
				String key = queryCondition.getKey();
				String op = queryCondition.getOp();
				sb.append(" and ");
				switch (op) {
				case QueryCondition.NOT_NULL:
					sb.append(key).append(op);
					break;
				case QueryCondition.IN:
				case QueryCondition.NOT_IN:
					sb.append(key).append(op).append(" ( ");
					@SuppressWarnings("unchecked")
					List<Object> listIn = (List<Object>)queryCondition.getValue();
					Iterator<Object> inIterator = listIn.iterator();
					while (inIterator.hasNext()) {
						Object value = inIterator.next();
						sb.append(" ?,");
						values.add(value);
						continue;
					}
					sb.deleteCharAt(sb.length() - 1).append(" ) ");
					break;
				case QueryCondition.LIKE:
					sb.append(key).append(" like ? ");
					values.add("%" + queryCondition.getValue() + "%");
					break;
				case QueryCondition.LEFT_LIKE:
					sb.append(key).append(" like ? ");
					values.add("%" + queryCondition.getValue());
					break;
				case QueryCondition.RIGHT_LIKE:
					sb.append(key).append(" like ? ");
					values.add(queryCondition.getValue() + "%");
					break;
				default:
					Object value = queryCondition.getValue();
					sb.append(key).append(op).append("?");
					values.add(value);
					break;
				}
			}
		}
		Map<String, Object> whereMap = new HashMap<String, Object>();
		// 检查是否需要过滤删除字段
		if (checkDelKey()) {
			sb.append(" and " + getDelName() + " <> " + getDelValue());
		}
		whereMap.put("sql", sb.toString());
		whereMap.put("values", values);
		return whereMap;
	}

	/**
	 * 拼装带有 order by 的 order 子句，如果conditionList中没有有效的项目，则返回空字符串
	 * @param conditionList 排序项目的List
	 * @return String
	 */
	protected String getOrderStr(List<OrderCondition> orderConditions) {
		StringBuilder sb = new StringBuilder();
		Iterator<OrderCondition> iterator = orderConditions.iterator();
		while (iterator.hasNext()) {
			OrderCondition condition = iterator.next();
			if (condition.getSortName() == null) {
				continue;
			}
			sb.append(", " + condition.getSortName() + " " + condition.getSortOrder() + " ");
		}
		String str = sb.toString();
		if (0 == str.length()) {
			return "order by id asc";		// oracle数据库分页默认不排序，如果不给排序方式，会出现数据重复
		}
		return " order by " + str.substring(1);
	}

	/**
	 * 根据search_col数组内容,返回 and col1 like '%keyWord%' or col2 like '%keyWord%'
	 * @param keyWord
	 * @return
	 */
	protected String buildSearchSql(String keyWord){
		if (null == keyWord || "".equals(keyWord)) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < getSearchCol().length; i++) {
			sb.append(" or "+ getSearchCol()[i]).append(" like '%"+keyWord+"%'");
		}
		if (0 == sb.length()) {
			return "";
		}
		return " and ( "+sb.toString().substring(3)+ " )";
	}
	/**
	 * 拼接group by 语句,如果传入为空,至少返回""
	 * @param gropuByParams
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	protected static String buildGroupBySql(List<String> gropuByParams)
	{
		StringBuilder sb = new StringBuilder();
		if (0 == gropuByParams.size()) {
			return "";
		}
		for (Iterator iterator = gropuByParams.iterator(); iterator.hasNext();) {
			String str = (String) iterator.next();
			if (StrKit.isBlank(str)) {
				continue;
			}
			sb.append(",").append(str).append(" ");
		}
		String sqlStr = sb.toString();
		if (0 == sqlStr.length()) {
			return "";
		}
		return " group by "+sqlStr.substring(1);
	}
	
	/**
	 * 单个group by
	 * @param gropuByParam
	 * @return
	 */
	protected String buildGroupBySql(String gropuByParam)
	{
		List<String> gropuByParams = new ArrayList<String>();
		gropuByParams.add(gropuByParam);
		return buildGroupBySql(gropuByParams);
	}

	/**
	 * 通用多条件分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @param queryConditionList	查询条件
	 * @param orderConditionList	排序字段及方式
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<M> paginate(int pageNumber, int pageSize, List<QueryCondition> queryConditionList, List<OrderCondition> orderConditionList) {
		Map<String, Object> whereMap = getWhereStr(queryConditionList);
		String orderString = getOrderStr(orderConditionList);
		String sql = " from " + getTableName() + " where " + whereMap.get("sql") + orderString;
//		logSql("select * " + sql, (List<Object>)whereMap.get("values"));
		return super.paginate(pageNumber, pageSize, getPreFixSql(), sql, ((List<Object>)whereMap.get("values")).toArray());
	}
	
	//打印语句
	public void logSql(String sql, List<Object> values) {
		System.out.println(sql);
		Iterator<Object> iterator = values.iterator();
		while (iterator.hasNext()) {
			Object value = iterator.next();
			sql = sql.replaceFirst("\\?", value.toString());
		}
		System.out.println("SQL: " + sql);
	}
	
	/**
	 * 多参数，单条件排序分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @param queryConditionList
	 * @param orderCondition
	 * @return
	 */
	public Page<M> paginate(int pageNumber, int pageSize, List<QueryCondition> queryConditionList, OrderCondition orderCondition) {
		List<OrderCondition> orderConditionList = new ArrayList<OrderCondition>();
		orderConditionList.add(orderCondition);
		return this.paginate(pageNumber, pageSize, queryConditionList, orderConditionList);
	}
	/**
	 * 单条件参数，多条件排序分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @param queryCondition
	 * @param orderConditionList
	 * @return
	 */
	public Page<M> paginate(int pageNumber, int pageSize, QueryCondition queryCondition, List<OrderCondition> orderConditionList) {
		List<QueryCondition> queryConditionList = new ArrayList<QueryCondition>();
		queryConditionList.add(queryCondition);
		return paginate(pageNumber, pageSize, queryConditionList, orderConditionList);
	}
	/**
	 * 单条件、排序分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @param queryCondition
	 * @param orderCondition
	 * @return
	 */
	public Page<M> paginate(int pageNumber, int pageSize, QueryCondition queryCondition, OrderCondition orderCondition) {
		List<OrderCondition> orderConditionList = new ArrayList<OrderCondition>();
		orderConditionList.add(orderCondition);
		List<QueryCondition> queryConditionList = new ArrayList<QueryCondition>();
		queryConditionList.add(queryCondition);
		return paginate(pageNumber, pageSize, queryConditionList, orderConditionList);
	}

	/**
	 * 无条件分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @param orderCondition	排序条件
	 * @return
	 */
	public Page<M> paginate(int pageNumber, int pageSize, OrderCondition orderCondition) {
		return paginate(pageNumber, pageSize, new ArrayList<QueryCondition>(), orderCondition);
	}
	/**
	 * 无条件，无排序分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public Page<M> paginate(int pageNumber, int pageSize) {
		return paginate(pageNumber, pageSize, new ArrayList<QueryCondition>(), new ArrayList<OrderCondition>());
	}
	/**
	 * 多条件，无排序分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @param queryCondition
	 * @return
	 */
	public Page<M> paginate(int pageNumber, int pageSize, List<QueryCondition> conditionList) {
		return paginate(pageNumber, pageSize, conditionList, new ArrayList<OrderCondition>());
	}
	
	/**
	 * 单条件,无排序分页查询
	 * @param pageNumber
	 * @param pageSize
	 * @param queryCondition
	 * @return
	 */
	public Page<M> paginate(int pageNumber, int pageSize, QueryCondition queryCondition) {
		List<QueryCondition> queryConditionList = new ArrayList<QueryCondition>();
		queryConditionList.add(queryCondition);
		return paginate(pageNumber, pageSize, queryConditionList, new ArrayList<OrderCondition>());
	}
	
	//TODO  重载
	@SuppressWarnings("unchecked")
	public Page<M> paginateByCache(String cacheName, String key, int pageNumber, int pageSize, 
			List<QueryCondition> queryConditionList, List<OrderCondition> orderConditionList) {
		Cache cache = Redis.use(cacheName);
		if (cache.exists(key)) {
			return cache.get(key);
		}
		Map<String, Object> whereMap = getWhereStr(queryConditionList);
		String orderString = getOrderStr(orderConditionList);
		String sql = " from " + getTableName() + " where " + whereMap.get("sql") + orderString;
		cache.setex(key, REDIS_CACHE_EXPIRE, find(sql.toString(), ((List<Object>)whereMap.get("values")).toArray()));
//		logSql("select * " + sql, (List<Object>)whereMap.get("values"));
		return super.paginate(pageNumber, pageSize, getPreFixSql(), sql, ((List<Object>)whereMap.get("values")).toArray());
	}
	
}
